using System;
using System.Collections.Generic;
using System.Text;
using System.Xml;
using System.Collections;
using System.Data.SqlClient;
using Microsoft.Win32;

namespace LoadTestController
{
    class QueryStats
    {
        private string m_prefix;
        
        private bool m_RunQueryStats = false;
        public bool RunQueryStats
        {
            get
            {
                return m_RunQueryStats;
            }
        }

        Hashtable m_SqlServers;
        ArrayList m_SqlServerCNN;

        public QueryStats(string prefix)
        {
            m_prefix = prefix;

            string configDirKeyPath = "SOFTWARE\\Microsoft\\MSCRM";
            string configDir = null;

            if (Registry.LocalMachine.OpenSubKey(configDirKeyPath) != null)
            {
                RegistryKey regKey = Registry.LocalMachine.OpenSubKey(configDirKeyPath);
                configDir = (string)regKey.GetValue("CRM_Perf_Toolkit_ConfigDir", "c:\\crmstress");
            }
            else
            {
                //setting the default directory of configsettings.xml to c:\crmstress folder
                configDir = "c:\\crmstress";
            }

            XmlDocument configSettings = new XmlDocument();
            configSettings.Load(configDir  + "\\configsettings.xml");

            XmlNode root = configSettings.DocumentElement;
            XmlNode queryStats = root.SelectSingleNode("querystats");

            if (queryStats != null)
            {
                if (queryStats.Attributes["value"] != null)
                {
                    m_RunQueryStats = bool.Parse(queryStats.Attributes["value"].Value);
                }
            }

            m_SqlServers = new Hashtable();
            m_SqlServerCNN = new ArrayList();
            XmlNode crmservers = root.SelectSingleNode("crmservers");
            XmlNodeList crmserverList = crmservers.SelectNodes("crmserver");

            for (int i = 0; i < crmserverList.Count; i++)
            {
                XmlNode sqlcnn = crmserverList[i].SelectSingleNode("sqlcnn");
                if (sqlcnn != null)
                {
                    if (sqlcnn.Attributes["value"] != null)
                    {
                        m_SqlServerCNN.Add(sqlcnn.Attributes["value"].Value);

                        SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
                        builder.ConnectionString = (string)m_SqlServerCNN[0];
                        if (m_SqlServers.ContainsKey((string)builder.DataSource) == false)
                        {
                            m_SqlServers.Add((string)builder.DataSource, (string)builder.DataSource);
                        }
                    }
                }
            }
        }

        public void FreeProcCache()
        {
            SqlConnection sqlConnection = null;
            foreach (string sqlServer in m_SqlServers.Keys)
            {
                SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
                builder.DataSource = sqlServer;
                builder.IntegratedSecurity = true;
                builder.InitialCatalog = "MASTER";

                try
                {
                    sqlConnection = new SqlConnection(builder.ConnectionString);
                    sqlConnection.Open();

                    SqlCommand sqlCommand = new SqlCommand();
                    sqlCommand.Connection = sqlConnection;
                    sqlCommand.CommandText = @"dbcc freeproccache";
                    sqlCommand.CommandTimeout = 0;
                    sqlCommand.CommandType = System.Data.CommandType.Text;
                    sqlCommand.ExecuteNonQuery();

                    sqlCommand = new SqlCommand();
                    sqlCommand.Connection = sqlConnection;
                    sqlCommand.CommandText = @"dbcc dropcleanbuffers";
                    sqlCommand.CommandTimeout = 0;
                    sqlCommand.CommandType = System.Data.CommandType.Text;
                    sqlCommand.ExecuteNonQuery();
                }
                catch (SqlException sqlEx)
                {
                    System.Diagnostics.Debug.WriteLine(sqlEx.ToString());
                }
                catch (Exception e)
                {
                    System.Diagnostics.Debug.WriteLine(e.ToString());
                }
                finally
                {
                    if (sqlConnection != null)
                    {
                        sqlConnection.Close();
                    }
                }
            }
        }

        public void CaptureQueryStats()
        {
            SqlConnection sqlConnection = null;
            foreach (string sqlServer in m_SqlServers.Keys)
            {
                SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
                builder.DataSource = sqlServer;
                builder.IntegratedSecurity = true;
                builder.InitialCatalog = "MASTER";

                try
                {
                    sqlConnection = new SqlConnection(builder.ConnectionString);
                    sqlConnection.Open();

                    SqlCommand sqlCommand = new SqlCommand();
                    sqlCommand.Connection = sqlConnection;
                    sqlCommand.CommandText = @"select
      substring (qt.text, (qs.statement_start_offset/2) + 1, ((case qs.statement_end_offset when -1 then datalength(qt.text) else qs.statement_end_offset end - qs.statement_start_offset)/2) + 1) as statment_text
      , qt.text as batch_text
      , qt.dbid as 'query_text_dbid'
      , qpa.Attribute
      , qpa.value as 'plan_attribute_dbid'
      , qs.plan_generation_num

      , qs.total_worker_time / qs.execution_count as 'avg_worker_time'
      , qs.total_physical_reads / qs.execution_count as 'avg_physcial_reads'
      , qs.total_logical_reads / qs.execution_count as 'avg_logical_reads'
      , qs.total_logical_writes / qs.execution_count as 'avg_logical_writes'
      , qs.total_elapsed_time / qs.execution_count as 'avg_elapsed_time'

      , qs.creation_time
      , qs.last_execution_time
      , qs.execution_count

      , qs.total_worker_time
      , qs.last_worker_time
      , qs.min_worker_time
      , qs.max_worker_time

      , qs.total_physical_reads
      , qs.last_physical_reads
      , qs.min_physical_reads
      , qs.max_physical_reads

      , qs.total_logical_reads
      , qs.last_logical_reads
      , qs.min_logical_reads
      , qs.max_logical_reads

      , qs.total_logical_writes
      , qs.last_logical_writes
      , qs.min_logical_writes
      , qs.max_logical_writes

      , qs.total_elapsed_time
      , qs.last_elapsed_time
      , qs.min_elapsed_time
      , qs.max_elapsed_time

      , qp.query_plan
into master.." + m_prefix + @"
from sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(qs.sql_handle) qt cross apply sys.dm_exec_query_plan(qs.plan_handle) qp cross apply sys.dm_exec_plan_attributes (qs.plan_handle) qpa
where qpa.Attribute = 'dbid'";
                    sqlCommand.CommandTimeout = 0;
                    sqlCommand.CommandType = System.Data.CommandType.Text;
                    sqlCommand.ExecuteNonQuery();
                }
                catch (SqlException sqlEx)
                {
                    System.Diagnostics.Debug.WriteLine(sqlEx.ToString());
                }
                catch (Exception e)
                {
                    System.Diagnostics.Debug.WriteLine(e.ToString());
                }
                finally
                {
                    if (sqlConnection != null)
                    {
                        sqlConnection.Close();
                    }
                }

            }

        }
    }
}
